var { pool } = require('../utils/dbUtil')

var moment = require('moment');

// 插入一级分类
function insertAType(obj) {
    let sql = `INSERT INTO atype(aTypeId,typeName,typeDescription,typeOrder,typeTime) VALUES(?,?,?,?,STR_TO_DATE(?,'%Y-%m-%d %T'))`;
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                connection.release()
                reject(connError)
            } else {
                let date = new Date()
                let timestamp = date.getTime();
                let time = moment(date).format("YYYY-MM-DD HH:mm:SS")
                let { typeName, typeDescription, typeOrder } = obj
                connection.query(sql, ['a' + timestamp, typeName, typeDescription, typeOrder, time], (queryError, rows) => {
                    if (queryError) {
                        connection.release()
                        reject(queryError)
                    } else {
                        connection.release()
                        resolve(rows)
                    }
                })
            }
        })
    })
}

// 插入二级分类
function insertBType(obj) {
    let sql = `INSERT INTO btype(aTypeId,bTypeId,typeName,typeDescription,typeOrder,typeTime) VALUES(?,?,?,?,?,STR_TO_DATE(?,'%Y-%m-%d %T'))`;
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                connection.release()
                reject(connError)
            } else {
                let date = new Date()
                let timestamp = date.getTime();
                let time = moment(date).format("YYYY-MM-DD HH:mm:SS")
                let { typeId, typeName, typeDescription, typeOrder } = obj
                connection.query(sql, [typeId, 'b' + timestamp, typeName, typeDescription, typeOrder, time], (queryError, rows) => {
                    if (queryError) {
                        connection.release()
                        reject(queryError)
                    } else {
                        connection.release()
                        resolve(rows)
                    }
                })
            }
        })
    })
}

// 获取所有一级分类
function selectAType() {
    let sql = 'SELECT aTypeId `typeId` , typeName FROM atype';
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                connection.query(sql, [], (queryError, rows) => {
                    if (queryError) {
                        connection.release()
                        reject(queryError)
                    } else {
                        connection.release()
                        resolve(rows)
                    }
                })
            }
        })
    })
}

// 获取一二级所有分类 id + name
function selectTypes() {
    let sql = 'SELECT aTypeId `typeId` , typeName FROM atype';
    let sql2 = 'SELECT bTypeId `typeId` , typeName FROM btype where aTypeId = ?';
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                connection.beginTransaction(err => {
                    if (err) {
                        reject(err)
                    } else {
                        connection.query(sql, [], (queryError, rows) => {
                            if (queryError) {
                                connection.release()
                                reject(queryError)
                            } else {
                                if (rows.length > 0) {
                                    let rt = rows.concat()
                                    for (let i = 0; i < rows.length; i++) {
                                        connection.query(sql2, [rows[i].typeId], (queryError2, rows2) => {
                                            if (queryError2) {
                                                connection.release()
                                                reject(queryError2)
                                            } else {
                                                rt[i].children = rows2
                                            }
                                            if (i == rows.length - 1) {
                                                connection.release()
                                                resolve(rt)
                                            }
                                        })
                                    }
                                } else {
                                    connection.release()
                                    resolve(rows)
                                }
                            }
                        })
                    }
                })
            }
        })
    })
}

// 查询所有分类数据
function selectAlltype() {
    let sql1 = 'SELECT aTypeId typeId,typeDescription,typeName,typeOrder,typeTime FROM atype order by typeOrder';
    let sql2 = 'SELECT aTypeId aid, bTypeId typeId,typeDescription,typeName,typeOrder,typeTime FROM btype where aTypeId = ? order by typeOrder'
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                connection.query(sql1, [], (queryError, rows) => {
                    if (queryError) {
                        connection.release()
                        reject(queryError)
                    } else {
                        let rt = rows.concat()
                        for (let i = 0; i < rows.length; i++) {
                            connection.query(sql2, [rows[i].typeId], (queryError2, rows2) => {
                                if (queryError2) {
                                    connection.release()
                                    reject(queryError2)
                                } else {
                                    rt[i].children = rows2
                                }
                                if (i == rows.length - 1) {
                                    connection.release()
                                    resolve(rt)
                                }
                            })
                        }
                    }
                })
            }
        })
    })
}

// 删除一级分类
function deleteAType(id) {
    let sql = 'delete FROM btype where aTypeId = ?';
    let sql2 = 'delete FROM atype where aTypeId = ?';
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                connection.beginTransaction(err => {
                    if (err) {
                        reject(err)
                    } else {
                        connection.query(sql, [id], (queryError, rows) => {
                            if (queryError) {
                                connection.release()
                                reject(queryError)
                            } else {
                                connection.query(sql2, [id], (queryError2, rows2) => {
                                    if (queryError2) {
                                        connection.release()
                                        reject(queryError2)
                                    } else {
                                        connection.commit(error => {
                                            if (error) {
                                                reject(error)
                                            } else {
                                                connection.release()
                                                resolve(rows2)
                                            }
                                        })
                                    }
                                })
                            }
                        })
                    }
                })
            }
        })
    })
}

// 删除二级分类
function deleteBType(id) {
    let sql = 'delete FROM btype where bTypeId = ?';
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                connection.query(sql, [id], (queryError, rows) => {
                    if (queryError) {
                        connection.release()
                        reject(queryError)
                    } else {
                        connection.release()
                        resolve(rows)
                    }
                })
            }
        })
    })
}

// 修改分类
function updateType(obj) {
    // console.log(obj)
    let sql1 = 'update atype set typeName=?,typeDescription=?,typeOrder=? where aTypeId = ?';
    let sql2 = 'update btype set typeName=?,typeDescription=?,typeOrder=? where bTypeId = ?';
    return new Promise((resolve, reject) => {
        pool.getConnection(function (connError, connection) {
            if (connError) {
                reject(connError)
            } else {
                let { typeId, typeName, typeDescription, typeOrder } = obj
                if (typeId.indexOf('a') != -1) {
                    connection.query(sql1, [typeName, typeDescription, typeOrder, typeId], (queryError, rows) => {
                        if (queryError) {
                            connection.release()
                            reject(queryError)
                        } else {
                            connection.release()
                            resolve(rows)
                        }
                    })
                } else {
                    console.log([typeName, typeDescription, typeOrder, typeId])
                    connection.query(sql2, [typeName, typeDescription, typeOrder, typeId], (queryError, rows) => {
                        if (queryError) {
                            connection.release()
                            reject(queryError)
                        } else {
                            connection.release()
                            resolve(rows)
                        }
                    })
                }
            }
        })
    })
}

module.exports = {
    insertAType,
    insertBType,
    selectAType,
    selectAlltype,
    deleteAType,
    deleteBType,
    updateType,
    selectTypes
}